using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Microsoft.Practices.WizardFramework;
using Microsoft.Practices.RepositoryFactory.CodeGeneration.ObjectModel;
using System.Collections;
using Microsoft.Practices.RepositoryFactory.SchemaDiscovery.ObjectModel.Base;
using Microsoft.Practices.RepositoryFactory.CodeGeneration.ObjectModel.Mappings;
using Microsoft.Practices.RepositoryFactory.CodeGeneration.ObjectModel.Mappings.Enums;
using Microsoft.Practices.RepositoryFactory.CodeGeneration.ObjectModel.NamingConventions;
using System.ComponentModel.Design;
using System.Text.RegularExpressions;
using System.Globalization;
using Resources=Microsoft.Practices.RepositoryFactory.Properties.Resources;

namespace Microsoft.Practices.RepositoryFactory.CustomPages
{
    public partial class SPsSelectorCP : CustomWizardPage
    {
        #region Fields

        private ArrayList tableStoredProcedureMappings;

        #endregion

        #region Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="T:SPsSelectorCP"/> class.
        /// </summary>
        /// <param name="parent">The parent.</param>
        public SPsSelectorCP(WizardForm parent)
            : base(parent)
        {
            InitializeComponent();
        }

        #endregion

        #region Arguments

        /// <summary>
        /// Gets or sets the table stored procedure mappings.
        /// </summary>
        /// <value>The table stored procedure mappings.</value>
        [RecipeArgument]
        public ArrayList TableStoredProcedureMappings
        {
            get
            {
                return this.tableStoredProcedureMappings;
            }
            set
            {
                this.tableStoredProcedureMappings = value;
            }
        }

        #endregion

        #region Event Handlers

        /// <summary>
        /// Called when [activated].
        /// </summary>
        public override void OnActivated()
        {
            IDictionaryService dictservice = (IDictionaryService)GetService(typeof(IDictionaryService));

            if(dictservice.GetValue("TableStoredProcedureMappings") == null)
            {
                lstTables.Items.Clear();
            }

            FillListBox();
            UpdateMapping();
            SaveArgument();
            base.OnActivated();
        }

        private void lstTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            DBTable dbTable = (DBTable)lstTables.SelectedItem;

            if(dbTable != null)
            {
                SetControls(dbTable);
            }
        }

        private void chkGenerateInsertProcedure_CheckedChanged(object sender, EventArgs e)
        {
            SetMappingControls(chkGenerateInsertProcedure, txtInsertProcedureName, StoredProcedureType.Insert);
        }

        private void chkGenerateUpdateProcedure_CheckedChanged(object sender, EventArgs e)
        {
            SetMappingControls(chkGenerateUpdateProcedure, txtUpdateProcedureName, StoredProcedureType.Update);
        }

        private void chkGenerateDeleteProcedure_CheckedChanged(object sender, EventArgs e)
        {
            SetMappingControls(chkGenerateDeleteProcedure, txtDeleteProcedureName, StoredProcedureType.Delete);
        }

        private void chkGenerateGetAllProcedure_CheckedChanged(object sender, EventArgs e)
        {
            SetMappingControls(chkGenerateGetAllProcedure, txtGetAllProcedureName, StoredProcedureType.GetAll);
        }

        private void chkGenerateGetByPkProcedure_CheckedChanged(object sender, EventArgs e)
        {
            SetMappingControls(chkGenerateGetByPkProcedure, txtGetByPKProcedureName, StoredProcedureType.GetByPK);
        }

        private void chkGenerateGetByFkProcedure_CheckedChanged(object sender, EventArgs e)
        {
            SetMappingControls(chkGenerateGetByFkProcedure, txtGetByFKProcedureName, StoredProcedureType.GetByFK);
        }

        private void chkGenerateInsertProcedure_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void txtInsertProcedureName_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void chkGenerateUpdateProcedure_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void chkGenerateDeleteProcedure_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void chkGenerateGetAllProcedure_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void chkGenerateGetByPkProcedure_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void chkGenerateGetByFkProcedure_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void txtUpdateProcedureName_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void txtDeleteProcedureName_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void txtGetAllProcedureName_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void txtGetByPKProcedureName_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        private void txtGetByFKProcedureName_Leave(object sender, EventArgs e)
        {
            UpdateMapping();
        }

        #endregion

        #region Private Implementation

        private ArrayList GetTables()
        {
            IDictionaryService dictservice = (IDictionaryService)GetService(typeof(IDictionaryService));
            ArrayList tables = (ArrayList)dictservice.GetValue("Tables");
            ArrayList dbTables = new ArrayList();

            foreach(Table table in tables)
            {
                DBTable dbTable = CreateDbTable(table);
                dbTables.Add(dbTable);
            }

            return dbTables;
        }

        private void SyncDBTables()
        {
            IDictionaryService dictservice = (IDictionaryService)GetService(typeof(IDictionaryService));
            ArrayList tables = (ArrayList)dictservice.GetValue("Tables");
            ArrayList tablesToRemove = new ArrayList();

            foreach(object item in lstTables.Items)
            {
                if(FindTable(tables, ((DBTable)item).Name) == null)
                {
                    tablesToRemove.Add(item);
                }
            }

            foreach(DBTable table in tablesToRemove)
            {
                lstTables.Items.Remove(table);
            }

            foreach(Table table in tables)
            {
                DBTable dbTable = CreateDbTable(table);

                if(FindDbTable(dbTable.Name) == null)
                {
                    lstTables.Items.Add(dbTable);
                }
            }
        }

        private DBTable FindDbTable(string tableName)
        {
            foreach(object item in lstTables.Items)
            {
                DBTable dbTable = (DBTable)item;

                if (string.Compare(dbTable.Name, tableName, StringComparison.InvariantCultureIgnoreCase) == 0)
                {
                    return dbTable;
                }
            }

            return null;
        }

        private Table FindTable(ArrayList tables, string tableName)
        {
            foreach(Table table in tables)
            {
                if(string.Compare(table.Name, tableName, StringComparison.InvariantCultureIgnoreCase) == 0)
                {
                    return table;
                }
            }

            return null;
        }

        private DBTable CreateDbTable(Table table)
        {
            DBTable dbTable = new DBTable();

            dbTable.Name = table.Name;
            dbTable.Schema = table.Schema;

            foreach(Column column in table.Columns)
            {
                dbTable.Columns.Add(column);
            }

            dbTable.StoredProcedureMappings.Add(new TableStoredProcedureMapping(table, StoredProcedureType.Insert));
            dbTable.StoredProcedureMappings.Add(new TableStoredProcedureMapping(table, StoredProcedureType.Update));
            dbTable.StoredProcedureMappings.Add(new TableStoredProcedureMapping(table, StoredProcedureType.Delete));
            dbTable.StoredProcedureMappings.Add(new TableStoredProcedureMapping(table, StoredProcedureType.GetAll));
            dbTable.StoredProcedureMappings.Add(new TableStoredProcedureMapping(table, StoredProcedureType.GetByPK));
            dbTable.StoredProcedureMappings.Add(new TableStoredProcedureMapping(table, StoredProcedureType.GetByFK));

            return dbTable;
        }

        private void UpdateMapping()
        {
            foreach(TableStoredProcedureMapping mapping in ((DBTable)lstTables.SelectedItem).StoredProcedureMappings)
            {
                switch(mapping.Type)
                {
                    case StoredProcedureType.Insert:
                        mapping.CodeGenerationEnabled = chkGenerateInsertProcedure.Checked;
                        mapping.Name = txtInsertProcedureName.Text;
                        break;
                    case StoredProcedureType.Delete:
                        mapping.CodeGenerationEnabled = chkGenerateDeleteProcedure.Checked;
                        mapping.Name = txtDeleteProcedureName.Text;
                        break;
                    case StoredProcedureType.Update:
                        mapping.CodeGenerationEnabled = chkGenerateUpdateProcedure.Checked;
                        mapping.Name = txtUpdateProcedureName.Text;
                        break;
                    case StoredProcedureType.GetByPK:
                        mapping.CodeGenerationEnabled = chkGenerateGetByPkProcedure.Checked;
                        mapping.Name = txtGetByPKProcedureName.Text;
                        break;
                    case StoredProcedureType.GetByFK:
                        mapping.CodeGenerationEnabled = chkGenerateGetByFkProcedure.Checked;
                        mapping.Name = txtGetByFKProcedureName.Text;
                        break;
                    case StoredProcedureType.GetAll:
                        mapping.CodeGenerationEnabled = chkGenerateGetAllProcedure.Checked;
                        mapping.Name = txtGetAllProcedureName.Text;
                        break;
                    default:
                        break;
                }
            }

            ValidateMappingProcedureNames();
            SaveArgument();
        }

        private void SetControls(DBTable dbTable)
        {
            infoProvider.SetError(txtInsertProcedureName, "");
            infoProvider.SetError(txtDeleteProcedureName, "");
            infoProvider.SetError(txtUpdateProcedureName, "");
            infoProvider.SetError(txtGetByPKProcedureName, "");
            infoProvider.SetError(txtGetByFKProcedureName, "");

            foreach(TableStoredProcedureMapping mapping in dbTable.StoredProcedureMappings)
            {
                switch(mapping.Type)
                {
                    case StoredProcedureType.Insert:
                        if(!TableHasOnlyTimeStampOrReadOnlyColumns(dbTable))
                        {
                            chkGenerateInsertProcedure.Enabled = true;
                            chkGenerateInsertProcedure.Checked = mapping.CodeGenerationEnabled;
                            txtInsertProcedureName.Text = mapping.Name;
                        }
                        else
                        {
                            chkGenerateInsertProcedure.Enabled = false;
                            chkGenerateInsertProcedure.Checked = false;
                            txtInsertProcedureName.Text = string.Empty;
                            infoProvider.SetError(txtInsertProcedureName, 
                                string.Format(
                                CultureInfo.CurrentCulture, 
                                Resources.TableWithTimeStampOrReadOnlyColumnsValidation,
                                Resources.InsertSPName));
                        }
                        break;
                    case StoredProcedureType.Delete:
                        if(TableHasPrimaryKeys(dbTable))
                        {
                            chkGenerateDeleteProcedure.Enabled = true;
                            chkGenerateDeleteProcedure.Checked = mapping.CodeGenerationEnabled;
                            txtDeleteProcedureName.Text = mapping.Name;
                        }
                        else
                        {
                            chkGenerateDeleteProcedure.Enabled = false;
                            chkGenerateDeleteProcedure.Checked = false;
                            txtDeleteProcedureName.Text = string.Empty;
                            infoProvider.SetError(txtDeleteProcedureName,
                                string.Format(
                                CultureInfo.CurrentCulture,
                                Resources.TableWithoutPrimaryKeyValidation,
                                Resources.DeleteSPName));
                        }
                        break;
                    case StoredProcedureType.Update:
                        if(TableHasPrimaryKeys(dbTable))
                        {
                            chkGenerateUpdateProcedure.Enabled = true;
                            chkGenerateUpdateProcedure.Checked = mapping.CodeGenerationEnabled;
                            txtUpdateProcedureName.Text = mapping.Name;
                        }
                        else
                        {
                            chkGenerateUpdateProcedure.Enabled = false;
                            chkGenerateUpdateProcedure.Checked = false;
                            txtUpdateProcedureName.Text = string.Empty;
                            infoProvider.SetError(txtUpdateProcedureName,
                                string.Format(
                                CultureInfo.CurrentCulture,
                                Resources.TableWithoutPrimaryKeyValidation,
                                Resources.UpdateSPName));
                        }
                        break;
                    case StoredProcedureType.GetByPK:
                        if(TableHasPrimaryKeys(dbTable))
                        {
                            chkGenerateGetByPkProcedure.Enabled = true;
                            chkGenerateGetByPkProcedure.Checked = mapping.CodeGenerationEnabled;
                            txtGetByPKProcedureName.Text = mapping.Name;
                        }
                        else
                        {
                            chkGenerateGetByPkProcedure.Enabled = false;
                            chkGenerateGetByPkProcedure.Checked = false;
                            txtGetByPKProcedureName.Text = string.Empty;
                            infoProvider.SetError(txtGetByPKProcedureName,
                                string.Format(
                                CultureInfo.CurrentCulture,
                                Resources.TableWithoutPrimaryKeyValidation,
                                Resources.GetByPkSPName));
                        }
                        break;
                    case StoredProcedureType.GetByFK:
                        if(GetForeignKeyColumns(dbTable).Count == 1)
                        {
                            chkGenerateGetByFkProcedure.Enabled = true;
                            chkGenerateGetByFkProcedure.Checked = mapping.CodeGenerationEnabled;
                            txtGetByFKProcedureName.Text = mapping.Name;
                        }
                        else 
                        {
                            chkGenerateGetByFkProcedure.Enabled = false;
                            chkGenerateGetByFkProcedure.Checked = false;
                            txtGetByFKProcedureName.Text = string.Empty;
                            infoProvider.SetError(txtGetByFKProcedureName,
                                string.Format(
                                CultureInfo.CurrentCulture,
                                Resources.TableWithZeroOrMoreThanOneFKValidation,
                                Resources.GetByFkSPName));                      
                        }
                        break;
                    case StoredProcedureType.GetAll:                        
                        chkGenerateGetAllProcedure.Checked = mapping.CodeGenerationEnabled;
                        txtGetAllProcedureName.Text = mapping.Name;
                        break;
                    default:
                        break;
                }
            }

            ValidateMappingProcedureNames();
        }

        private bool ValidateMappingProcedureNames()
        {
            bool isValid = true;
            errorProvider.Clear();

            foreach (DBTable table in lstTables.Items)
            {
                foreach (TableStoredProcedureMapping mapping in table.StoredProcedureMappings)
                {
                    if (mapping.CodeGenerationEnabled && !ValidateProcedureName(mapping.Name))
                    {
                        isValid = false;

                        if (lstTables.SelectedItem == table)
                        {
                            TextBox procedureTextBox = null;
                            switch (mapping.Type)
                            {
                                case StoredProcedureType.Insert:
                                    procedureTextBox = txtInsertProcedureName;
                                    break;
                                case StoredProcedureType.Update:
                                    procedureTextBox = txtUpdateProcedureName;
                                    break;
                                case StoredProcedureType.Delete:
                                    procedureTextBox = txtDeleteProcedureName;
                                    break;
                                case StoredProcedureType.GetAll:
                                    procedureTextBox = txtGetAllProcedureName;
                                    break;
                                case StoredProcedureType.GetByPK:
                                    procedureTextBox = txtGetByPKProcedureName;
                                    break;
                                case StoredProcedureType.GetByFK:
                                    procedureTextBox = txtGetByFKProcedureName;
                                    break;
                            }
                            if (procedureTextBox != null)
                                errorProvider.SetError(procedureTextBox, Resources.InvalidProcedureNameError);
                        }
                    }
                }
            }

            return isValid;
        }

        private bool ValidateProcedureName(string name)
        {
            return Regex.IsMatch(name, "^\\w+$");
        }

        private List<Column> GetForeignKeyColumns(DBTable table)
        {
            List<Column> columns = new List<Column>();

            if(table != null)
            {
                foreach(Column column in table.Columns)
                {
                    if(column.IsForeignKey)
                    {
                        columns.Add(column);
                    } 
                }
            }

            return columns;
        }

        private bool TableHasPrimaryKeys(DBTable table)
        {
            bool response = false;

            if(table != null)
            {
                foreach(Column column in table.Columns)
                {
                    if(column.IsPrimaryKey)
                    {
                        response = true;
                        break;
                    }
                }
            }

            return response;
        }

        private bool TableHasOnlyTimeStampOrReadOnlyColumns(DBTable table)
        {
            bool onlyReadOnlyColumns = true;
            bool onlyTimeStampColumns = true;

            if(table != null)
            {
                foreach(Column column in table.Columns)
                {
                    if(!column.IsReadOnly)
                    {
                        onlyReadOnlyColumns = false;
                        break;
                    }
                    
                    if(column.DbDataType.ProviderType != DatabaseType.Timestamp)
                    {
                        onlyTimeStampColumns = false;
                        break;
                    }
                }
            }

            return (onlyReadOnlyColumns && onlyTimeStampColumns);
        }

        private void FillListBox()
        {
            lstTables.DisplayMember = "DBTable.FullName";

            if(lstTables.Items.Count == 0)
            {
                ArrayList dbTables = GetTables();

                foreach(DBTable dbTable in dbTables)
                {
                    lstTables.Items.Add(dbTable);
                }
            }
            else
            {
                SyncDBTables();
            }

            if(lstTables.Items.Count > 0)
            {
                lstTables.SelectedIndex = 0;
            }
        }

        private void SetMappingControls(CheckBox checkBox, TextBox textBox, StoredProcedureType type)
        {
            if(checkBox.Checked)
            {
                textBox.Enabled = true;
                textBox.Text = NamingHelper.GetSPName((DBTable)lstTables.SelectedItem, type);
            }
            else
            {
                textBox.Enabled = false;
                textBox.Text = string.Empty;
            }

            ValidateMappingProcedureNames();
        }

        private ArrayList GetTableSPMappings()
        {
            ArrayList mappings = new ArrayList();

            foreach(object item in lstTables.Items)
            {
                mappings.Add((DBTable)item);
            }

            return mappings;
        }

        private void SaveArgument()
        {
            ArrayList dbTables = GetTableSPMappings();
            IDictionaryService dictservice = (IDictionaryService)GetService(typeof(IDictionaryService));
            dictservice.SetValue("TableStoredProcedureMappings", dbTables);
        }

        public override bool IsDataValid
        {
            get
            {
                return base.IsDataValid && ValidateMappingProcedureNames();
            }
        }
        #endregion
    }
}